CREATE PROC [dbo].[STIGetInfoAcc] @FactorId DEC(20, 0)
AS
BEGIN
    /*---------------Date--------1403/02/19-------- Declare RKara variables---------------- */

    DECLARE @InvoiceKind SMALLINT = 0;
    DECLARE @Stamp BIGINT;
    DECLARE @TotalReceivedAmount DECIMAL(20, 0) = 0;
    DECLARE @SalesInvoiceId DECIMAL(20, 0) = 0;
    DECLARE @SalesInvoiceNo VARCHAR(20) = '';
    DECLARE @ReturnInvoiceId DECIMAL(20, 0);
    DECLARE @ReturnInvoiceNo DECIMAL(20, 0);
    DECLARE @GuyCode VARCHAR(5);
    DECLARE @EconomicCode VARCHAR(MAX);
    DECLARE @CompanyEconomicCode VARCHAR(MAX);
    DECLARE @NetTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @ReduceTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @AfterReduceTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @TaxTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @InvoiceTotalAmount DECIMAL(20, 0);
    DECLARE @ReceiptAmount DECIMAL(20, 0) = 0;
    DECLARE @CashAndChequeAmount DECIMAL(20, 0) = 0;
    DECLARE @DecimalZero DECIMAL(1, 0) = 0;
    DECLARE @BuyerPostalCode VARCHAR(10);
    DECLARE @TypeOfBuyer TINYINT;
    DECLARE @ReferenceTaxId VARCHAR(MAX) = NULL;
    DECLARE @TypeOfInvoice INT;
    DECLARE @BuyerBranchCode VARCHAR(MAX);
    DECLARE @SellerBranchCode VARCHAR(MAX);
    DECLARE @Insp DECIMAL(20, 0) = 0;
    DECLARE @Cap DECIMAL(20, 0) = 0;
    DECLARE @Setm INT = 0;
    --                                         

    /*----------------  Get Company Economic Code ---------------- */

    SELECT TOP (1)
           @CompanyEconomicCode = ISNULL(c.CodeEghtesadi, NULL),
           @SellerBranchCode = c.SellerBranchCode
    FROM Manager.dbo.Cooprations c
    WHERE AutoId =
    (
        SELECT AutoId FROM Manager.dbo.Years WHERE NameYear = DB_NAME()
    );

    /*---------------- Declaer Result Table---------------- */
    DECLARE @Detail TABLE
    (
        Kind SMALLINT NOT NULL
            DEFAULT (0),
        GoodsCode VARCHAR(15) NOT NULL,
        DeliverCount DEC(20, 3) NOT NULL,
        SalesAmount MONEY NOT NULL,
        ReducedAmounts DEC(20, 0) NOT NULL,
        HasTax BIT NOT NULL
            DEFAULT (0),
        TaxPrice DECIMAL(20, 0) NOT NULL
            DEFAULT (0),
        TaxPercent DEC(2, 0) NOT NULL
            DEFAULT (0)
    );

    ----------------
    --                 
    --    PrescriptionKind =>             ѐ                    1  ѐ    2         3

    /*------------------------ Detect Prescription Type---------------- */
    SELECT @InvoiceKind = CASE
                              WHEN H.Kind_Flag IN ( 5, 7, 9 ) THEN
                                  1
                              WHEN H.Kind_Flag IN ( 0, 6, 8 ) THEN
                                  2
                          END,
           @SalesInvoiceNo = CASE
                                 WHEN H.Kind_Flag IN ( 5, 7, 9 ) THEN
                                     H.Nu_Forosh
                                 ELSE
                                     0
                             END,
           @ReturnInvoiceNo = CASE
                                  WHEN H.Kind_Flag IN ( 0, 6, 8 ) THEN
                                      H.Nu_Bargasht_Forosh
                                  ELSE
                                      0
                              END,
           @SalesInvoiceId = CASE
                                 WHEN H.Kind_Flag IN ( 5, 7, 9 ) THEN
                                     H.Id_Havaleh
                                 ELSE
                                     0
                             END,
           @ReturnInvoiceId = CASE
                                  WHEN H.Kind_Flag IN ( 0, 6, 8 ) THEN
                                      H.Id_Havaleh
                                  ELSE
                                      0
                              END,
           @Stamp = REPLACE(dbo.MiladiToShamsi(H.Tarikh_faktor), '/', '') + REPLICATE('0', 4)
    FROM dbo.headH H WITH (NOLOCK)
    WHERE H.Id_Havaleh = @FactorId;

    /*----------------------------------------  Print Values -------------------------*/

    /*SELECT @InvoiceKind PrescriptionKind,
           @SalesInvoiceNo SalesInvoiceNo,
           @ReturnInvoiceNo ReturnInvoiceNo,
           @SalesInvoiceId SalesInvoiceId,
           @ReturnInvoiceId ReturnInvoiceId;
		   */

    /*------------------------- Detect Reference Invoice------------------------------*/
    IF @InvoiceKind = 2
    BEGIN
        SELECT @SalesInvoiceId = H.Id_Havaleh,
               @SalesInvoiceNo = H.Nu_Forosh
        FROM dbo.headH H WITH (NOLOCK)
        WHERE H.Kind_Flag IN ( 5, 7, 9 )
              AND H.Nu_Forosh IN
                  (
                      SELECT TOP (1)
                             Nu_Forosh
                      FROM dbo.headH WITH (NOLOCK)
                      WHERE Id_Havaleh = @FactorId
                            AND Kind_Flag IN ( 0, 6, 8 )
                      ORDER BY Tarikh DESC
                  );
        /*----------------------------- Insert Detail ------------------------------*/
        SET NOCOUNT ON;
        INSERT INTO @Detail -- Reference 
        SELECT 1,
               D.K_Code,
               SUM(D.K_Qty1),
               (SUM(D.K_Qty1 * D.Price_Forosh)),
               ISNULL(SUM(R.ReducePrice), 0),
               G.Duty_Flag,
               ISNULL(
                         FLOOR(SUM(FLOOR((D.K_Qty1 * D.Price_Forosh) - R.ReducePrice) * (.01 * ISNULL(R.TaxPercent, 0)))),
                         0
                     ),
               ISNULL(SUM(R.TaxPercent), 0)
        FROM dbo.Havaleh D WITH (NOLOCK)
            JOIN dbo.KalaId G WITH (NOLOCK)
                ON G.K_Code = D.K_Code
            LEFT JOIN
            (
                SELECT R.AutoId,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 2 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) ReducePrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) TaxPrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.[Percent]
                                  ELSE
                                      0
                              END
                          ) TaxPercent
                FROM dbo.Over_Under_Row R
                    JOIN dbo.Over_Under RD
                        ON RD.AutoId = R.Code_Over_Under
                           AND ISNULL(RD.TaxTransactionType, 0) <> 0
                    JOIN dbo.Havaleh D
                        ON D.Id_Havaleh = @SalesInvoiceId
                           AND D.AutoId = R.AutoId
                GROUP BY R.AutoId
            ) AS R
                ON R.AutoId = D.AutoId
        WHERE D.Id_Havaleh = @SalesInvoiceId
        GROUP BY D.K_Code,
                 G.Duty_Flag,
                 R.AutoId;

        /*-------------------------------------------------------*/

        INSERT INTO @Detail --@ReturnDetail
        SELECT 2,
               D.K_Code,
               SUM(D.K_Qty1),
               (SUM(D.K_Qty1 * D.Price_Forosh)),
               ISNULL(SUM(R.ReducePrice), 0),
               G.Duty_Flag,
               ISNULL(
                         FLOOR(SUM(FLOOR((D.K_Qty1 * D.Price_Forosh) - R.ReducePrice) * (.01 * ISNULL(R.TaxPercent, 0)))),
                         0
                     ),
               ISNULL(SUM(R.TaxPercent), 0)
        FROM dbo.Havaleh D WITH (NOLOCK)
            JOIN dbo.KalaId G
                ON G.K_Code = D.K_Code
            LEFT JOIN
            (
                SELECT R.AutoId,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 2 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) ReducePrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) TaxPrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.[Percent]
                                  ELSE
                                      0
                              END
                          ) TaxPercent
                FROM dbo.Over_Under_Row R
                    JOIN dbo.Over_Under RD
                        ON RD.AutoId = R.Code_Over_Under
                           AND ISNULL(RD.TaxTransactionType, 0) <> 0
                    JOIN dbo.Havaleh D --1402 12 13
                        ON D.Id_Havaleh = @ReturnInvoiceId
                           AND D.AutoId = R.AutoId
                GROUP BY R.AutoId
            ) AS R
                ON R.AutoId = D.AutoId
        WHERE D.Id_Havaleh = @ReturnInvoiceId
        GROUP BY D.K_Code,
                 G.Duty_Flag,
                 R.AutoId;
        /*---------------- Insert Not Return---------------------------*/
        INSERT INTO @Detail --@ResultDetail 
        SELECT 3,
               resultTable.GoodsCode,
               SUM(resultTable.DeliverCount),
               SUM(resultTable.SalesAmount),
               SUM(resultTable.ReducedAmounts),
               resultTable.HasTax,
               SUM(resultTable.TaxPrice),
               MAX(resultTable.TaxPercent)
        FROM
        (
            SELECT GoodsCode,
                   DeliverCount,
                   SalesAmount,
                   ReducedAmounts,
                   HasTax,
                   TaxPrice,
                   TaxPercent
            FROM @Detail
            WHERE Kind = 1
            UNION
            SELECT GoodsCode,
                   -1 * DeliverCount,
                   -1 * SalesAmount,
                   -1 * ReducedAmounts,
                   HasTax,
                   -1 * TaxPrice,
                   TaxPercent
            FROM @Detail
            WHERE Kind = 2
        ) AS resultTable
        GROUP BY resultTable.GoodsCode,
                 resultTable.HasTax;

        DELETE FROM @Detail
        WHERE DeliverCount <= 0;
        /*----------------------- Detect Invoice is Cancelled ???----------------------------*/
        IF NOT EXISTS (SELECT 1 FROM @Detail WHERE Kind = 3)
            SET @InvoiceKind = 3;
        /*----------------------------------- Set The Recipient of the invoice-----------------*/
        SELECT TOP (1)
               @GuyCode = D.Sender
        FROM dbo.Havaleh D
        WHERE Id_Havaleh = @ReturnInvoiceId;
    /*------------------------- End Of Return Invoice -------------------------*/
    END;

    IF @InvoiceKind = 1
       OR @InvoiceKind = 3
    BEGIN

        /*--------------------------- Prepare Detail -------------------------*/
        SET NOCOUNT ON;
        INSERT INTO @Detail -- Result 
        (
            Kind,
            GoodsCode,
            DeliverCount,
            SalesAmount,
            ReducedAmounts,
            HasTax,
            TaxPrice,
            TaxPercent
        )
        SELECT 3,
               D.K_Code,
               SUM(D.K_Qty1),
               (SUM(D.K_Qty1 * D.Price_Forosh)),
               ISNULL(SUM(R.ReducePrice), 0),
               G.Duty_Flag,
               ISNULL(
                         FLOOR(SUM(FLOOR((D.K_Qty1 * D.Price_Forosh) - R.ReducePrice) * (.01 * ISNULL(R.TaxPercent, 0)))),
                         0
                     ),
               ISNULL(SUM(R.TaxPercent), 0)
        FROM dbo.Havaleh D WITH (NOLOCK)
            JOIN dbo.KalaId G
                ON G.K_Code = D.K_Code
            LEFT JOIN
            (
                SELECT R.AutoId,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 2 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) ReducePrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) TaxPrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.[Percent]
                                  ELSE
                                      0
                              END
                          ) TaxPercent
                FROM dbo.Over_Under_Row R
                    JOIN dbo.Over_Under RD
                        ON RD.AutoId = R.Code_Over_Under
                           AND ISNULL(RD.TaxTransactionType, 0) <> 0
                    JOIN dbo.Havaleh D
                        ON D.Id_Havaleh = @SalesInvoiceId
                           AND D.AutoId = R.AutoId
                GROUP BY R.AutoId
            ) AS R
                ON R.AutoId = D.AutoId
        WHERE D.Id_Havaleh = @SalesInvoiceId
        GROUP BY D.K_Code,
                 G.Duty_Flag,
                 R.AutoId;
    END;

    /*-------------------------- Find TaxId -------------------------*/

    IF @InvoiceKind = 2
       OR @InvoiceKind = 3
    BEGIN
        SELECT @ReferenceTaxId = TaxId
        FROM dbo.STITransaction
        WHERE InvoiceId = @SalesInvoiceId
              AND [Type] = 5
              AND [Status] = 1;
    END;

    /*----------------------- Calc POS And Cash Receipt Amount -------------------------*/

    SELECT @ReceiptAmount = SUM(R.Price)
    FROM dbo.Over_Under_Factor R
        JOIN dbo.Over_Under RD
            ON RD.AutoId = R.Code_Over_Under
               AND ISNULL(RD.TaxTransactionType, 0) = 1
    WHERE R.Id_Havaleh = @FactorId;
    /*------------------------ Calc Header Total Amount -------------------------*/

    SELECT @NetTotalAmount = SUM(FLOOR(D.SalesAmount)),
           @ReduceTotalAmount = FLOOR(SUM(D.ReducedAmounts)),
           @AfterReduceTotalAmount = SUM(FLOOR(D.SalesAmount - D.ReducedAmounts)),
           @TaxTotalAmount = FLOOR(SUM(D.TaxPrice)),
           @InvoiceTotalAmount = SUM(FLOOR((D.SalesAmount - D.ReducedAmounts) + D.TaxPrice))
    FROM @Detail D
    WHERE Kind = 3;

    /*----------------------------------------- Set The Recipient of the invoice -------------------------*/
    SELECT TOP (1)
           @GuyCode = D.Reciver
    FROM dbo.Havaleh D
    WHERE Id_Havaleh = @SalesInvoiceId;


    --   SELECT *
    --   FROM @Detail
    --WHERE Kind = 3;

    /*--------------------- Get Econmic Code of Person -------------------------*/

    SELECT @BuyerPostalCode = ISNULL(G.Postal_Code, NULL),
           @TypeOfBuyer = CASE
                              WHEN ISNULL(I.HcForoshandeTypeCode, 1) IN ( 0, 1 ) THEN
                                  1
                              ELSE
                                  I.HcForoshandeTypeCode
                          END,
           @EconomicCode = CASE
                               WHEN (
                                        G.Tax_Code IS NULL
                                        OR G.Tax_Code = ''
                                    )
                                    AND LEN(G.economic_code) <> 14 THEN
                                   NULL
                               ELSE
                                   CASE
                                       WHEN @TypeOfBuyer = 1
                                            AND LEN(G.economic_code) = 14 THEN
                                           G.economic_code
                                       ELSE
                                           G.Tax_Code
                                   END
                           END,
           @TypeOfInvoice = CASE
                                WHEN ISNULL(I.HcForoshandeTypeCode, 0) = 0 THEN
                                    '2'
                                ELSE
                                    '1'
                            END,
           @BuyerBranchCode = I.BuyerBranchCode
    FROM dbo.Guy G
        LEFT JOIN dbo.tblGuy_Info I
            ON I.Guy_Code = G.Guy_Code
    WHERE G.Guy_Code = @GuyCode;

    /*----------------- Calc Cash and Check Amount -------------------------*/
    SELECT @CashAndChequeAmount = SUM(Ch_Price)
    FROM dbo.Cheqe
    WHERE Id_Cheqe =
    (
        SELECT TOP (1)
               H.Id_Cheqe
        FROM dbo.HeadC H
        WHERE Id_Havaleh = @FactorId
        ORDER BY H.Id_Cheqe
    );

    /*-------------------------------------------Print Value-------------------------*/
    --SELECT @NetTotalAmount NetTotalAmount,
    --       @ReduceTotalAmount ReduceTotalAmount,
    --       @AfterReduceTotalAmount AfterReduceTotalAmount,
    --       @TaxTotalAmount TaxTotalAmount,
    --       @InvoiceTotalAmount InvoiceTotalAmount,
    -- @ReceiptAmount ReceiptAmount, 
    --       @CashAndChequeAmount CashAndChequeAmount;
    --RETURN;

    /*--------------------  Calculate Inoive Header Variable -------------------------*/

    IF @BuyerBranchCode = '' set @BuyerBranchCode = NULL
    SET @TotalReceivedAmount
        = ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0) - ISNULL(@ReduceTotalAmount, 0);
    IF @TotalReceivedAmount < 0
        SET @TotalReceivedAmount = 0;


    SET @Insp
        = CASE
              WHEN (@InvoiceTotalAmount
                    - ((ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0) + ISNULL(@TaxTotalAmount, 0)))
                   ) < 0 THEN
                  0
              WHEN @TotalReceivedAmount > 0 THEN
                  @InvoiceTotalAmount - (ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0))
              ELSE
    (@InvoiceTotalAmount
     - ((ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0) + CASE
                                                                           WHEN @TotalReceivedAmount > 0 THEN
                                                                               ISNULL(@TaxTotalAmount, 0)
                                                                           ELSE
                                                                               0
                                                                       END
        )
       )
    )
          END;


    SET @Cap
        = CASE
              WHEN (ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0) - ISNULL(@ReduceTotalAmount, 0)) < 0 THEN
                  0
              ELSE
    (ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0) - ISNULL(@ReduceTotalAmount, 0))
          END;



    SET @Setm = CASE
                    WHEN (@InvoiceTotalAmount - @ReduceTotalAmount) - @TotalReceivedAmount > 10
                         AND @TotalReceivedAmount > 0 THEN
                        3
                    WHEN @TotalReceivedAmount = 0 THEN
                        2
                    ELSE
                        1
                END;

    IF @TypeOfInvoice = 2
    BEGIN
        SET @Insp = 0;
        SET @Cap = @AfterReduceTotalAmount;
        SET @Setm = 1;
    END;
    /*--------------------  Select Inoive Header --------------------*/
    SELECT @AfterReduceTotalAmount tadis,
           @ReduceTotalAmount tdis,
           @NetTotalAmount tprdis,
           @TaxTotalAmount tvam,
           @InvoiceTotalAmount tbill,
           cap = @Cap,
           insp = @Insp,
           '' taxid,
           @BuyerBranchCode bbc,
           CASE
               WHEN LEN(@EconomicCode) = 10
                    AND @TypeOfInvoice = 1 THEN
                   @EconomicCode
               ELSE
                   NULL
           END bid,
           '' billid,
           CASE
               WHEN @BuyerPostalCode IS NULL
                    OR @BuyerPostalCode = ''
                    OR @TypeOfInvoice = 2 THEN
                   NULL
               ELSE
                   @BuyerPostalCode
           END bpc,
           --'' crn,
           0 dpvb,
           @Stamp indatim,
           @Stamp indati2m,
           inno = DB_NAME() + CAST(CASE
                                       WHEN @InvoiceKind IN ( 2, 3 ) THEN
                                           @ReturnInvoiceNo
                                       ELSE
                                           @SalesInvoiceNo
                                   END AS VARCHAR(MAX)),
           irinno = CASE
                        WHEN @InvoiceKind = 1 THEN
                            NULL
                        ELSE
                            DB_NAME() + CAST(@SalesInvoiceNo AS VARCHAR(MAX))
                    END,
           1 inp,
           ins = CASE
                     WHEN @InvoiceKind = 1 THEN
                         1
                     WHEN @InvoiceKind = 2 THEN
                         4
                     WHEN @InvoiceKind = 3 THEN
                         3
                 END,
           @TypeOfInvoice inty,
           @ReferenceTaxId irtaxid,
           @SellerBranchCode sbc, --Changed
           NULL scc,              --Changed
           '' scln,
           setm = @Setm,
           @DecimalZero tax17,
           CASE
               WHEN LEN(@EconomicCode) > 10
                    AND @TypeOfInvoice = 1 THEN
                   @EconomicCode
               ELSE
                   NULL
           END tinb,
           @CompanyEconomicCode tins,
           @TypeOfBuyer tob,
           @DecimalZero todam,
           @DecimalZero tvop;

    /*--------------------SELECT DETAILS--------------------------*/

    SELECT D.DeliverCount am,
           ROUND(CAST((D.SalesAmount / D.DeliverCount) AS DEC(20, 2)), 2) fee,
           CAST(FLOOR(D.SalesAmount) AS DEC(20, 0)) prdis,
           D.ReducedAmounts dis,
           CAST(FLOOR(D.SalesAmount - D.ReducedAmounts) AS DEC(20, 0)) adis,
           D.TaxPercent vra,
           D.TaxPrice vam,
           CAST(FLOOR((D.SalesAmount - D.ReducedAmounts) + D.TaxPrice) AS DEC(20, 0)) tsstam,
           --@DecimalZero bros,
           --'' bsrn,
           ROUND(CAST((D.SalesAmount / D.DeliverCount) AS MONEY), 2) cfee, --Changed
           @DecimalZero consfee,
           @DecimalZero cop,
           NULL cut,                                                       --Changed
           NULL exr,                                                       --Changed
           CAST(ISNULL(UM.TaxCode, '1627') AS VARCHAR(10)) mu,
           @DecimalZero odam,
           @DecimalZero odr,
           '0' odt,
           @DecimalZero olam,
           @DecimalZero olr,
           '0' olt,
           @DecimalZero spro,
           ISNULL(
                     (ISNULL(GM.TaxCode, STUFF(REPLICATE('0', 13), 14 - LEN(G.K_Code), LEN(G.K_Code), G.K_Code))),
                     SUBSTRING(G.K_Code, 1, 13)
                 ) sstid,
           CASE
               WHEN ISNULL(GM.TaxName, '') = '' THEN
                   G.Name1
               ELSE
                   GM.TaxName
           END AS sstt,
           --@DecimalZero tcpbs,
           @DecimalZero vop
    FROM @Detail D -- Result
        JOIN dbo.KalaId G WITH (NOLOCK)
            ON G.K_Code = D.GoodsCode
        LEFT JOIN dbo.STIGoodsMapping GM
            ON GM.GoodsCode = G.K_Code
        LEFT JOIN dbo.STIUnitMaping UM
            ON UM.UnitCode = G.U_Code1
    WHERE D.Kind = 3;

    /*---------------------------------------------PAYMENT -------------------------*/
    SELECT '' acn,  --Changed
           '' iinn, --Changed
           '' pcn,  --Changed
           (CAST(NULL AS BIGINT)) pdt,
           '' pid,  --Changed
           '' trmn, --Changed
           '' trn;  --Changed
END;